package dataGateway;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import domainLogic.Bid;


/**
 * @author  jungmins
 */
public class BidTable {

	/**
	 * @uml.property  name="instance"
	 * @uml.associationEnd  
	 */
	private static BidTable instance = new BidTable();
	
	/**
	 * @return
	 * @uml.property  name="instance"
	 */
	public static BidTable getInstance()
	{
		return instance;
	}
		
	public BidTable() {  }
	
	private Connection getConnection() throws Exception {
		return ConnectionPool.getInstance().getConnection();
	} //getConnection()

	public synchronized boolean insertBid(Bid bid) {
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean ret = false;
		
		try {
			conn = getConnection();
			String sql = "insert into bids(item_id, user_id, price, biddate) "+
					"values(?,?,?,CURRENT_TIMESTAMP)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,bid.getItemId()); 
			pstmt.setString(2,bid.getUserId());
			pstmt.setDouble(3,bid.getPrice());			
			if(pstmt.executeUpdate()>0)
				ret = true;
			
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("insertBid: "+(System.currentTimeMillis()-startTime));
		
		return ret;
	} //insertMember

	public List<Bid> getBidUserAll(int userId)  {
		return getBidSql("select * from bids where user_id ="+userId +" order by price desc");
	}
	public List<Bid> getBidItemAll(int itemId)  {
		return getBidSql("select * from bids where item_id ="+itemId +" order by price desc");
	}
	
	public Bid getBidMaxPrice(int itemId) {
		List<Bid> items = getBidSql("select * from bids where item_id ="+itemId +" order by price desc");
		
		if(items != null && items.size() > 0)
			return items.get(0);
		
		return null;
	}
	public int getMaxPrice(int itemId) {
		Bid b = getBidMaxPrice(itemId);
		
		if(b == null)
			return 0;
				
		return b.getPrice();
	}
	public int getNumBids(int itemId)
	{
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery("select count(*) as numbids from bids where item_id ="+itemId );
			
			if(rs.next()) 
			{
				return rs.getInt("numbids");
			} 
		} catch(Exception ex) {
					ex.printStackTrace();
		} finally {
			if(rs    != null) try {    rs.close(); } catch(SQLException ex) { }		
			if(stmt != null) try { stmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("getNumBids: "+(System.currentTimeMillis()-startTime));

		return 0;
	}
	
	public List<Bid> getBidSql(String sql) 
	{
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<Bid> ret = new ArrayList<Bid>();
		
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) 
			{
				ret.add(new Bid(rs));
			} 
		} catch(Exception ex) {
					ex.printStackTrace();
		} finally {
			if(rs    != null) try {    rs.close(); } catch(SQLException ex) { }		
			if(stmt != null) try { stmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("getBidSql: "+(System.currentTimeMillis()-startTime));

		return ret;
	}
	

} //class
